from sqlalchemy import Column, String, Integer, DateTime, DECIMAL
from sqlalchemy.orm import declarative_base

Base = declarative_base()


class KwmsWorkPool(Base):
    """
    CREATE TABLE `wops_kwms_work_pool` (
      `sub_task_id` varchar(32) NOT NULL COMMENT '子任务编号',
      `parent_sub_id` varchar(32) NOT NULL,
      `sku` varchar(32) NOT NULL COMMENT '物料sku',
      `equ_code` varchar(32) NOT NULL COMMENT '作业站台',
      `equ_type` int(11) NOT NULL COMMENT '设备类型',
      `origin_work_loc` varchar(32) NOT NULL COMMENT '起点',
      `target_work_loc` varchar(32) NOT NULL COMMENT '终点',
      `origin_lpn` varchar(32) NOT NULL COMMENT '作业LPN',
      `target_lpn` varchar(32) NOT NULL,
      `origin_pack_type` int(10) DEFAULT NULL COMMENT '开始物料形态1：栈板 2：件箱 3：周转箱',
      `target_pack_type` int(10) DEFAULT NULL COMMENT '结束物料形态1：栈板 2：件箱 3：周转箱',
      `origin_sku_num` int(11) NOT NULL COMMENT '初始货量',
      `target_sku_num` int(11) NOT NULL COMMENT '目标货量',
      `operate_pack_type` int(11) NOT NULL COMMENT '1 栈板、2纸箱、3.周转箱、4MPQ(最小包装)',
      `operate_pack_number` int(11) NOT NULL COMMENT '作业数量',
      `state` int(10) NOT NULL COMMENT '4已完成',
      `plan_begin` datetime DEFAULT NULL COMMENT '计划开始时间',
      `plan_end` datetime DEFAULT NULL COMMENT '计划结束时间',
      `wops_work_resourceid` int(11) NOT NULL COMMENT '所属工序',
      `real_begin` datetime DEFAULT NULL COMMENT '实际开始时间',
      `real_end` datetime DEFAULT NULL,
      PRIMARY KEY (`sub_task_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='执行作业池'
    """
    __tablename__ = 'wops_kwms_work_pool'

    sub_task_id = Column(String(32), primary_key=True, nullable=False, comment='子任务编号')
    parent_sub_id = Column(String(32), nullable=False, comment='上级任务编号')
    sku = Column(String(32), nullable=False, comment='物料sku')
    equ_code = Column(String(32), nullable=False, comment='作业站台')
    equ_type = Column(Integer, nullable=False, comment='设备类型')
    origin_work_loc = Column(String(32), nullable=False, comment='起点')
    target_work_loc = Column(String(32), nullable=False, comment='终点')
    origin_lpn = Column(String(32), nullable=False, comment='作业LPN')
    target_lpn = Column(String(32), nullable=False)
    origin_pack_type = Column(Integer, comment='开始物料形态1：栈板 2：件箱 3：周转箱')
    target_pack_type = Column(Integer, comment='结束物料形态1：栈板 2：件箱 3：周转箱')
    origin_sku_num = Column(Integer, nullable=False, comment='初始货量')
    target_sku_num = Column(Integer, nullable=False, comment='目标货量')
    operate_pack_type = Column(Integer, nullable=False, comment='1 栈板、2纸箱、3.周转箱、4MPQ(最小包装)')
    operate_pack_number = Column(Integer, nullable=False, comment='作业数量')
    state = Column(Integer, nullable=False, comment='4已完成')
    plan_begin = Column(DateTime, comment='计划开始时间')
    plan_end = Column(DateTime, comment='计划结束时间')
    wops_work_resourceid = Column(Integer, nullable=False, comment='所属工序')
    real_begin = Column(DateTime, comment='实际开始时间')
    real_end = Column(DateTime)
    time_cost = Column(DECIMAL(10, 3), default=0, comment='子任务耗时')


class KwmsWorkQueue(Base):
    """
    CREATE TABLE `wops_kwms_work_queue` (
      `task_id` varchar(32) NOT NULL COMMENT '母任务编号',
      `parent_task_id` varchar(255) DEFAULT NULL COMMENT '上级任务，无上级任务则为空，多个上级任务用逗号分隔',
      `command_type_code` varchar(32) NOT NULL COMMENT '指令类型编号',
      `command_group_code` varchar(32) NOT NULL COMMENT '指令组编号',
      `lpn` varchar(32) NOT NULL COMMENT '作业LPN',
      `is_urgent` int(10) DEFAULT NULL COMMENT '急料标识',
      `sku` varchar(32) NOT NULL COMMENT '物料sku',
      `origin_sku_num` int(10) NOT NULL,
      `target_sku_num` int(10) NOT NULL,
      `origin_pack_type` int(10) NOT NULL COMMENT '开始物料类型',
      `target_pack_type` int(10) NOT NULL COMMENT '结束物料类型',
      `origin_loc` varchar(32) NOT NULL COMMENT '开始货位',
      `origin_loc_template_detailid` varchar(32) DEFAULT NULL COMMENT '开始货位货格（针对柔性货位使用）',
      `state` int(10) DEFAULT NULL COMMENT '工单状态\r\n工单状态1 新建、 2:已排程、3、已下发、4已完成、5已取消',
      `plan_begin` datetime DEFAULT NULL COMMENT '计划开始时间',
      `plan_end` datetime DEFAULT NULL COMMENT '计划结束时间',
      `wops_wavelet_orderid` varchar(32) NOT NULL COMMENT '小波次编号',
      `wops_command_unit_routeid` varchar(32) NOT NULL COMMENT '作业路径编号',
      `wops_vehicle_taskid` varchar(32) DEFAULT NULL COMMENT '发车任务编号',
      `wops_delivery_time_windowid` varchar(32) NOT NULL COMMENT '大波次编号',
      `time_window_start_time` datetime NOT NULL COMMENT '时间窗开始时间',
      `time_window_end_time` datetime NOT NULL COMMENT '世界窗结束时间',
      `vehicle_task_begin_time` datetime DEFAULT NULL COMMENT '装车开始时间',
      `vehicle_task_end_time` datetime DEFAULT NULL COMMENT '装车结束时间',
      `add_date` datetime NOT NULL COMMENT '创建日期',
      `edit_date` datetime NOT NULL COMMENT '编辑日期',
      `loc_column` int(11) DEFAULT NULL COMMENT '列',
      `loc_row` int(11) DEFAULT NULL COMMENT '货位行',
      `template_id` varchar(32) DEFAULT NULL COMMENT '模板编号',
      `deliver_factory_code` varchar(32) NOT NULL,
      `deliver_factory` varchar(32) NOT NULL,
      `real_begin` datetime DEFAULT NULL,
      `real_end` datetime DEFAULT NULL,
      PRIMARY KEY (`task_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='执行作业队列'
    """

    __tablename__ = 'wops_kwms_work_queue'

    task_id = Column(String(32), primary_key=True, nullable=False, comment='母任务编号')
    parent_task_id = Column(String(255), comment='上级任务，无上级任务则为空，多个上级任务用逗号分隔')
    command_type_code = Column(String(32), nullable=False, comment='指令类型编号')
    command_group_code = Column(String(32), nullable=False, comment='指令组编号')
    lpn = Column(String(32), nullable=False, comment='作业LPN')
    is_urgent = Column(Integer, comment='急料标识')
    sku = Column(String(32), nullable=False, comment='物料sku')
    origin_sku_num = Column(Integer, nullable=False)
    target_sku_num = Column(Integer, nullable=False)
    origin_pack_type = Column(Integer, nullable=False, comment='开始物料类型')
    target_pack_type = Column(Integer, nullable=False, comment='结束物料类型')
    origin_loc = Column(String(32), nullable=False, comment='开始货位')
    origin_loc_template_detailid = Column(String(32), comment='开始货位货格（针对柔性货位使用）')
    state = Column(Integer, comment='工单状态\r\n工单状态1 新建、 2:已排程、3、已下发、4已完成、5已取消')
    plan_begin = Column(DateTime, comment='计划开始时间')
    plan_end = Column(DateTime, comment='计划结束时间')
    wops_wavelet_orderid = Column(String(32), nullable=False, comment='小波次编号')
    wops_command_unit_routeid = Column(String(32), nullable=False, comment='作业路径编号')
    wops_vehicle_taskid = Column(String(32), comment='发车任务编号')
    wops_delivery_time_windowid = Column(String(32), nullable=False, comment='大波次编号')
    time_window_start_time = Column(DateTime, nullable=False, comment='时间窗开始时间')
    time_window_end_time = Column(DateTime, nullable=False, comment='世界窗结束时间')
    vehicle_task_begin_time = Column(DateTime, comment='装车开始时间')
    vehicle_task_end_time = Column(DateTime, comment='装车结束时间')
    add_date = Column(DateTime, nullable=False, comment='创建日期')
    edit_date = Column(DateTime, nullable=False, comment='编辑日期')
    loc_column = Column(Integer, comment='列')
    loc_row = Column(Integer, comment='货位行')
    template_id = Column(String(32), comment='模板编号')
    deliver_factory_code = Column(String(32), nullable=False)
    deliver_factory = Column(String(32), nullable=False)
    real_begin = Column(DateTime, comment='实际开始时间')
    real_end = Column(DateTime, comment='实际结束时间')
    packkey = Column(String(50), default=None, comment='包规')
    innerpack = Column(DECIMAL(22, 5), default=None, comment='内包装换算率')
    casecant = Column(DECIMAL(22, 5), default=None, comment='件箱换算率')
    pallet = Column(DECIMAL(22, 5), default=None, comment='托盘换算率')
    task_key = Column(String(64))
    orderdetil = Column(String(256), comment='关联的订单编号')
    group_id = Column(String(64))
    lot = Column(String(256), comment='当前任务选中物料批次号')
    act_num = Column(Integer, comment='多批次栈板实际数量')

    # 扩展
    ext_pool_list: list[KwmsWorkPool] = []

    def __init__(self, kwargs):
        for k in kwargs:
            if hasattr(self, k) and not k.startswith('_'):
                setattr(self, k, kwargs[k])


class KwmsQueuePoolRelation(Base):
    """
    CREATE TABLE `wops_kwms_queue_pool_ralation` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `wops_kwms_work_queue_task_id` varchar(32) NOT NULL,
      `wops_kwms_work_pool_sub_task_id` varchar(32) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4
    """

    __tablename__ = 'wops_kwms_queue_pool_ralation'

    id = Column(Integer, primary_key=True, autoincrement=True, nullable=False)
    wops_kwms_work_queue_task_id = Column(String(32), nullable=False)
    wops_kwms_work_pool_sub_task_id = Column(String(32), nullable=False)

    def __init__(self, qid, pid):
        self.wops_kwms_work_queue_task_id = qid
        self.wops_kwms_work_pool_sub_task_id = pid


if __name__ == '__main__':
    from x_pc.db.kwms import Session

    with Session() as sess:
        all_data = sess.query(KwmsQueuePoolRelation).all()
        print(all_data)
